package net.risesoft.risebi.collection.service.impl;

import java.text.SimpleDateFormat;
import java.util.*;
import net.risesoft.risebi.collection.dao.ReportInstanceDao;
import net.risesoft.risebi.collection.model.*;
import net.risesoft.risebi.collection.service.*;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.appfuse.dao.Dao;
import org.appfuse.service.impl.BaseManager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 * 
 * 
 * @author <a href="mailto: baiyibing@gmail.com">王锤</a>
 * @version $Revision$
 * 
 */
@Repository(value = "reportInstanceManager")
@Transactional(rollbackFor = { java.lang.Exception.class })
public class ReportInstanceManagerImpl extends BaseManager
    implements ReportInstanceManager
{

    public ReportInstanceManagerImpl()
    {
    }

    public Map getPagedReportInstancesByObject(ReportInstance reportInstance, int start, int limit)
        throws Exception
    {
        String hql = getSqlFindObj(reportInstance);
        String countHql = (new StringBuilder("select count(*) ")).append(hql).toString();
        Integer count = getCount(countHql);
        List list = find(hql, start, limit);
        Map map = new HashMap();
        map.put("count", count);
        map.put("list", list);
        return map;
    }

    public ReportInstance getReportInstanceBySoCode(String appGuid, String templateGuid, String reportPeriodGuid, String soCode)
        throws Exception
    {
        String soInstanceGuid = "";
        List soList = catalogManager.getCatalogWithSoByApp(appGuid, false);
        for(Iterator iterator = soList.iterator(); iterator.hasNext();)
        {
            Catalog c = (Catalog)iterator.next();
            String tableName = c.getTableName().toLowerCase();
            List list = find((new StringBuilder("select new map(item_code as code, item_guid as guid) from ")).append(tableName).append(" as t where t.item_code='").append(soCode).append("'").toString());
            if(list != null && !list.isEmpty())
            {
                HashMap map = (HashMap)list.get(0);
                soInstanceGuid = (String)map.get("guid");
                break;
            }
        }

        if(soInstanceGuid.length() == 0)
        {
            return null;
        } else
        {
            String hql = (new StringBuilder("from ReportInstance as r where r.template.guid='")).append(templateGuid).append("' and r.reportPeriod.guid='").append(reportPeriodGuid).append("'").append(" and r.soInstanceGuid='").append(soInstanceGuid).append("'").toString();
            return (ReportInstance)getUniqueObject(hql);
        }
    }

    public String getNativeQurySql4SystemUser(String databaseName, User user, Boolean inWindow, String reportVerifyType, Integer lockStatus, Integer reportStatus, String filter, 
            String selectedServiceGuid, String selectedAppGuid, String selectedSoCollectionGuid, String selectedStartSotNodeGuid, String selectedStartSotNodeInstanceGuid, String selectedEndSotNodeGuid)
        throws Exception
    {
        if(user.getUserType().intValue() != 1)
            throw new Exception();
        boolean isSuperAdmin = user.isSuperAdmin();
        String baseSql = "FROM Rpt7_ReportInstance ri WHERE 1=1 \r\n";
        if(!isSuperAdmin && selectedServiceGuid != null && selectedServiceGuid.trim().length() > 0)
        {
            String array[] = selectedServiceGuid.split(",");
            if(array.length == 1)
            {
                baseSql = (new StringBuilder("FROM Rpt7_ReportInstance ri WHERE ri.Service_guid='")).append(selectedServiceGuid).append("' \r\n").toString();
            } else
            {
                String guids = "";
                String as[];
                int k = (as = array).length;
                for(int i = 0; i < k; i++)
                {
                    String str = as[i];
                    if(guids.length() == 0)
                        guids = (new StringBuilder("'")).append(str).append("'").toString();
                    else
                        guids = (new StringBuilder(String.valueOf(guids))).append(",'").append(str).append("'").toString();
                }

                baseSql = (new StringBuilder("FROM Rpt7_ReportInstance ri WHERE ri.Service_guid in (")).append(guids).append(") \r\n").toString();
            }
        }
        if(selectedAppGuid != null && selectedAppGuid.trim().length() > 0)
        {
            String array[] = selectedAppGuid.split(",");
            if(array.length == 1)
            {
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.Application_guid='").append(selectedAppGuid).append("' \r\n").toString();
            } else
            {
                String guids = "";
                String as1[];
                int l = (as1 = array).length;
                for(int j = 0; j < l; j++)
                {
                    String str = as1[j];
                    if(guids.length() == 0)
                        guids = (new StringBuilder("'")).append(str).append("'").toString();
                    else
                        guids = (new StringBuilder(String.valueOf(guids))).append(",'").append(str).append("'").toString();
                }

                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.Application_guid in (").append(guids).append(") \r\n").toString();
            }
        }
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String currentDateTime = simpleDateFormat.format(new Date());
        if(inWindow != null && inWindow.booleanValue())
        {
            if("oracle".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t1 WHERE t1.unlockDate<to_date('").append(currentDateTime).append("', 'YYYY-MM-DD HH24:MI:SS') AND t1.lockDate>to_date('").append(currentDateTime).append("', 'YYYY-MM-DD HH24:MI:SS')) \r\n").toString();
            else
            if("mssql".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t1 WHERE t1.unlockDate<convert(datetime, '").append(currentDateTime).append("', 120) AND t1.lockDate>convert(datetime, '").append(currentDateTime).append("', 120)) \r\n").toString();
            if("mysql".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t1 WHERE t1.unlockDate<STR_TO_DATE('").append(currentDateTime).append("', '%Y-%m-%d %H:%i:%s') AND t1.lockDate>STR_TO_DATE('").append(currentDateTime).append("', '%Y-%m-%d %H:%i:%s')) \r\n").toString();
        } else
        {
            if("oracle".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t2.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t2 WHERE t2.unlockDate<to_date('").append(currentDateTime).append("', 'YYYY-MM-DD HH24:MI:SS')) \r\n").toString();
            else
            if("mssql".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t2.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t2 WHERE t2.unlockDate<convert(datetime, '").append(currentDateTime).append("', 120)) \r\n").toString();
            if("mysql".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t2.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t2 WHERE t2.unlockDate<STR_TO_DATE('").append(currentDateTime).append("', '%Y-%m-%d %H:%i:%s')) \r\n").toString();
        }
        if(reportVerifyType != null)
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportInstance_Guid IN (SELECT reportInstanceGuid from Rpt7_ReportVerifyMessage WHERE hidden != 1 AND type IN (").append(reportVerifyType).append(")) \r\n").toString();
        if(lockStatus != null)
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.lockStatus=").append(lockStatus).append(" \r\n").toString();
        if(reportStatus != null)
            if(reportStatus.intValue() == 1)
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.LevelCode_Submitted is null \r\n").toString();
            else
            if(reportStatus.intValue() == 2)
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.LevelCode_Submitted is not null \r\n").toString();
        if(filter != null && filter.trim().length() > 0)
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ").append(filter).append(" \r\n").toString();
        String subQuery = "";
        if(selectedSoCollectionGuid != null && selectedSoCollectionGuid.trim().length() > 0)
            subQuery = (new StringBuilder("(")).append(soCollectionManager.getSocMembersSQL(selectedSoCollectionGuid, false)).append(")").toString();
        else
        if(!isSuperAdmin)
        {
            List lst = employeeSoCollectionManager.getByUserAndType(selectedAppGuid, user, "1,2,3");
            for(Iterator iterator = lst.iterator(); iterator.hasNext();)
            {
                EmployeeSoCollection t = (EmployeeSoCollection)iterator.next();
                if(subQuery.length() == 0)
                    subQuery = (new StringBuilder("(")).append(soCollectionManager.getSocMembersSQL(t.getSoCollection().getGuid(), false)).append(")").toString();
                else
                    subQuery = (new StringBuilder(String.valueOf(subQuery))).append("\r\n union \r\n(").append(soCollectionManager.getSocMembersSQL(t.getSoCollection().getGuid(), false)).append(")").toString();
            }

        }
        if(selectedStartSotNodeGuid != null && selectedStartSotNodeGuid.trim().length() > 0 && selectedStartSotNodeInstanceGuid != null && selectedStartSotNodeInstanceGuid.trim().length() > 0)
            if(subQuery.length() == 0)
                subQuery = (new StringBuilder("(")).append(soCollectionManager.getSoTreeSQL(selectedStartSotNodeGuid, selectedStartSotNodeInstanceGuid, selectedEndSotNodeGuid, false)).append(")").toString();
            else
            if("mssql".equals(databaseName) || "oracle".equals(databaseName))
                subQuery = (new StringBuilder(String.valueOf(subQuery))).append("\r\n INTERSECT \r\n(").append(soCollectionManager.getSoTreeSQL(selectedStartSotNodeGuid, selectedStartSotNodeInstanceGuid, selectedEndSotNodeGuid, false)).append(")").toString();
            else
                subQuery = (new StringBuilder("SELECT t.item_guid FROM (\r\n")).append(subQuery).append("\r\n").append(") AS t WHERE t.item_guid IN (\r\n").append(soCollectionManager.getSoTreeSQL(selectedStartSotNodeGuid, selectedStartSotNodeInstanceGuid, selectedEndSotNodeGuid, false)).append(")").toString();
        if(subQuery.length() > 0)
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.so_instance_guid IN ( \r\n").append(subQuery).append("\r\n").append(") \r\n").toString();
        return baseSql;
    }

    public String getNativeQurySql4EnterpriseUser(String databaseName, User user, Boolean inWindow, String reportVerifyType, Integer lockStatus, Integer reportStatus, String filter)
        throws Exception
    {
        if(user.getUserType().intValue() != 2)
            throw new Exception();
        String baseSql = "FROM Rpt7_ReportInstance ri, rpt7_application app WHERE ri.Application_guid=app.app_guid AND app.app_status<3 AND app.app_inner=0 \r\n";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String currentDateTime = simpleDateFormat.format(new Date());
        if(inWindow != null && inWindow.booleanValue())
        {
            if("oracle".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t1 WHERE t1.unlockDate<to_date('").append(currentDateTime).append("', 'YYYY-MM-DD HH24:MI:SS') AND t1.lockDate>to_date('").append(currentDateTime).append("', 'YYYY-MM-DD HH24:MI:SS')) \r\n").toString();
            else
            if("mssql".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t1 WHERE t1.unlockDate<convert(datetime, '").append(currentDateTime).append("', 120) AND t1.lockDate>convert(datetime, '").append(currentDateTime).append("', 120)) \r\n").toString();
            if("mysql".equals(databaseName))
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t1.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t1 WHERE t1.unlockDate<STR_TO_DATE('").append(currentDateTime).append("', '%Y-%m-%d %H:%i:%s') AND t1.lockDate>STR_TO_DATE('").append(currentDateTime).append("', '%Y-%m-%d %H:%i:%s')) \r\n").toString();
        }
        if("oracle".equals(databaseName))
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t2.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t2 WHERE t2.unlockDate<to_date('").append(currentDateTime).append("', 'YYYY-MM-DD HH24:MI:SS')) \r\n").toString();
        else
        if("mssql".equals(databaseName))
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t2.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t2 WHERE t2.unlockDate<convert(datetime, '").append(currentDateTime).append("', 120)) \r\n").toString();
        if("mysql".equals(databaseName))
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportPeriod_Template_guid IN (SELECT t2.ReportPeriod_Template_guid from Rpt7_ReportPeriod_Template t2 WHERE t2.unlockDate<STR_TO_DATE('").append(currentDateTime).append("', '%Y-%m-%d %H:%i:%s')) \r\n").toString();
        if(reportVerifyType != null)
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.ReportInstance_Guid IN (SELECT reportInstanceGuid from Rpt7_ReportVerifyMessage WHERE hidden != 1 AND type IN (").append(reportVerifyType).append(")) \r\n").toString();
        if(lockStatus != null)
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.lockStatus=").append(lockStatus).append(" \r\n").toString();
        if(reportStatus != null)
            if(reportStatus.intValue() == 1)
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.LevelCode_Submitted is null \r\n").toString();
            else
            if(reportStatus.intValue() == 2)
                baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.LevelCode_Submitted is not null \r\n").toString();
        if(filter != null && filter.trim().length() > 0)
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ").append(filter).append(" \r\n").toString();
        String subQuery = "";
        if(user.getParentUser() == null)
        {
            log.debug((new StringBuilder("user.getGuid()==")).append(user.getGuid()).toString());
            List lst = userSOManager.getUserSOByUid(user.getGuid());
            for(Iterator iterator = lst.iterator(); iterator.hasNext();)
            {
                UserSO uso = (UserSO)iterator.next();
                StatisticalObjectTree tree = uso.getSo().getApplication().getDefaultSoTree();
                if(tree == null || tree.getGuid().trim().length() == 0)
                {
                    if(subQuery.length() == 0)
                        subQuery = (new StringBuilder("'")).append(uso.getSoInstanceGuid()).append("'").toString();
                    else
                        subQuery = (new StringBuilder(String.valueOf(subQuery))).append(",'").append(uso.getSoInstanceGuid()).append("'").toString();
                } else
                {
                    StatisticalObjectTreeNode startNode = tree.getNodeByCatalog(uso.getSo().getGuid());
                    List soInstancesList = soCollectionManager.find(soCollectionManager.getSoTreeSQL(startNode.getGuid(), uso.getSoInstanceGuid(), null, false), true);
                    for(Iterator iterator2 = soInstancesList.iterator(); iterator2.hasNext();)
                    {
                        Object o = iterator2.next();
                        if(subQuery.length() == 0)
                            subQuery = (new StringBuilder("'")).append(o.toString().trim()).append("'").toString();
                        else
                            subQuery = (new StringBuilder(String.valueOf(subQuery))).append(",'").append(o.toString().trim()).append("'").toString();
                    }

                }
            }

        } else
        {
            List lst = userSOManager.getUserSOByUid(user.getGuid());
            for(Iterator iterator1 = lst.iterator(); iterator1.hasNext();)
            {
                UserSO uso = (UserSO)iterator1.next();
                if(subQuery.length() == 0)
                    subQuery = (new StringBuilder("'")).append(uso.getSoInstanceGuid()).append("'").toString();
                else
                    subQuery = (new StringBuilder(String.valueOf(subQuery))).append(",'").append(uso.getSoInstanceGuid()).append("'").toString();
            }

        }
        if(subQuery.length() > 0)
            baseSql = (new StringBuilder(String.valueOf(baseSql))).append("AND ri.so_instance_guid IN ( \r\n").append(subQuery).append("\r\n").append(") \r\n").toString();
        return baseSql;
    }

    public List getUrgencyOrLateReportInstance(int type, List reportPeriodTypes, String serviceGuid, Date currentDate, int start, int limit)
        throws Exception
    {
        StringBuffer sb = new StringBuffer();
        sb.append("from ReportInstance r where r.status=0 and (");
        boolean first = true;
        for(Iterator iterator = reportPeriodTypes.iterator(); iterator.hasNext();)
        {
            String rpt = (String)iterator.next();
            if(first)
            {
                sb.append("r.reportPeriodType.guid='").append(rpt).append("' ");
                first = false;
            } else
            {
                sb.append("or r.reportPeriodType.guid='").append(rpt).append("' ");
            }
        }

        sb.append(") and r.service.guid='").append(serviceGuid).append("'");
        if(type == 1)
            sb.append(" and r.reportWindowPeriod.urgeDate < :currentDate ");
        else
        if(type == 2)
            sb.append(" and r.reportWindowPeriod.lateDate < :currentDate ");
        else
            sb.append(" and (r.reportWindowPeriod.urgeDate < :currentDate or r.reportWindowPeriod.lateDate < :currentDate)");
        String hql = sb.toString();
        List list = findByNamedParam(hql, new String[] {
            "currentDate"
        }, new Object[] {
            currentDate
        }, start, limit);
        return list;
    }

    public void lockServiceReportInstance(String serviceGuid, Date currentDate)
        throws Exception
    {
        String sql = (new StringBuilder("update Rpt7_ReportInstance r set r.lockStatus=1 where r.Service_guid='")).append(serviceGuid).append("' and r.ReportPeriod_Template_guid in (select ReportPeriod_Template_guid from rpt7_reportperiod_template t where t.lockDate < :currentDate)").toString();
        executeNativeSql(sql, new String[] {
            "currentDate"
        }, new Object[] {
            currentDate
        });
        dynaBeanManager.clearSecondCache();
    }

    public ReportInstanceDao getReportInstanceDao() {
		return reportInstanceDao;
	}

	@Autowired
	public void setReportInstanceDao(ReportInstanceDao reportInstanceDao) {
		this.reportInstanceDao = reportInstanceDao;
	}

	@SuppressWarnings("unchecked")
	public Dao getDao() {
		return reportInstanceDao;
	}

	public SoCollectionManager getSoCollectionManager() {
		return soCollectionManager;
	}

	@Autowired
	public void setSoCollectionManager(SoCollectionManager soCollectionManager) {
		this.soCollectionManager = soCollectionManager;
	}

	public EmployeeSoCollectionManager getEmployeeSoCollectionManager() {
		return employeeSoCollectionManager;
	}

	@Autowired
	public void setEmployeeSoCollectionManager(
			EmployeeSoCollectionManager employeeSoCollectionManager) {
		this.employeeSoCollectionManager = employeeSoCollectionManager;
	}

	public UserSOManager getUserSOManager() {
		return userSOManager;
	}

	@Autowired
	public void setUserSOManager(UserSOManager userSOManager) {
		this.userSOManager = userSOManager;
	}
	
	@Autowired
	public void setDynaBeanManager(DynaBeanManager dynaBeanManager) {
		this.dynaBeanManager = dynaBeanManager;
	}


    public CatalogManager getCatalogManager() {
		return catalogManager;
	}

	@Autowired
	public void setCatalogManager(CatalogManager catalogManager) {
		this.catalogManager = catalogManager;
	}

	public DynaBeanManager getDynaBeanManager() {
		return dynaBeanManager;
	}


	private final Log log = LogFactory.getLog(getClass());
    private ReportInstanceDao reportInstanceDao;
    private SoCollectionManager soCollectionManager;
    private EmployeeSoCollectionManager employeeSoCollectionManager;
    private UserSOManager userSOManager;
    private DynaBeanManager dynaBeanManager;
    private CatalogManager catalogManager;
}